﻿using NLog;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace DataPersistence
{
    public class PersistenceData
    {
        private static Logger logger = LogManager.GetCurrentClassLogger();
        Database db = new Database();

        /// <summary>
        /// 持久化数据
        /// </summary>
        /// <param name="dbConfig">数据库配置</param>
        /// <param name="comm">SqlCommand实例</param>
        /// <returns></returns>
        public int ExeSqlCode(SqlCommand comm,string DBName)
        {
            int line = 0;
            SqlConnection sconn = db.dataConnection(DBName);
            comm.Connection = sconn;
            sconn.Open();
            SqlTransaction tran = sconn.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                comm.Transaction = tran;
                line = comm.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                tran.Rollback();
            }
            finally
            {
                sconn.Close();
            }
            return line;
        }

        /// <summary>
        /// 持久化数据带GO
        /// </summary>
        /// <param name="comm">SqlCommand实例</param>
        /// <returns></returns>
        public int ExeSqlCodeWithGo(string sqlCode, string DBName)
        {
            int effectedRows = 0;
            using (SqlConnection conn = db.dataConnection(DBName))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                try
                {
                    //注：此处以 换行_后面带0到多个空格_再后面是go 来分割字符串  
                    String[] sqlArr = Regex.Split(sqlCode.Trim(), "\r\n\\s*go", RegexOptions.IgnoreCase);
                    foreach (string strsql in sqlArr)
                    {
                        if (strsql.Trim().Length > 1 && strsql.Trim() != "\r\n")
                        {
                            cmd.CommandText = strsql;
                            effectedRows += cmd.ExecuteNonQuery();
                        }
                    }
                }
                catch (SqlException ex)
                {
                    logger.Error(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return effectedRows;
        }
    }
}
